﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel.Channels;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Security.Cryptography;
using System.Text;
using System.Data.Sql;
using System.Data.OleDb;
using DAO;
using System.Windows.Forms;

/// <summary>
/// Summary description for UserDAO
/// </summary>
public class UserDAO
{
    #region laythongTin
    public static DataTable LayThongTinUserVuaDangKi(UserDTO ndDTO)
    { 
        string sQuery = "select * from UserN where UserName ='"+ndDTO.Username+"'";
        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        string s = GiaiMa(dt.Rows[0][0].ToString(), dt.Rows[0][1].ToString());
        dt.Rows[0][1] = s;
        return dt;
    }
    public static DataTable LayDanhSachUser()
    {
        string sQuery = "select * from[UserN";
        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        DataProvider.DongKetNoi(con);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            string s = GiaiMa(dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString());
            dt.Rows[i][1] = s;
        }
        return dt;
    }
    public static bool ktraTonTai(UserDTO ndDTO)            
    {
            string sQuery = "select * from UserN where UserName='" + ndDTO.Username + "'";            
            SqlConnection con = DataProvider.TaoKetNoi();
            DataTable dt = DataProvider.LayDataTable(sQuery, con);
            DataProvider.DongKetNoi(con);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
    }
    public static bool ktraDangNhap(UserDTO ndDTO)
    {
        string name = ndDTO.Username.Trim();
        string pass = ndDTO.Pass.Trim();
        //string sQuery = "select * from UserN where UserName=";
        //sQuery += "'";
        //sQuery += name;
        //sQuery += "'  AND  [Password]=";
        //sQuery += "'";
        //sQuery += pass;
        //sQuery += "'";
        //    //+MaHoa(ndDTO.Username,ndDTO.Pass)+"'";
        //SqlConnection con = DataProvider.TaoKetNoi();
        //con.Open();
        //DataTable dt = DataProvider.LayDataTable(sQuery, con);
        

       
        //DataProvider.DongKetNoi(con);
        //if (dt.Rows.Count > 0)
        //{
        //    con.Close();
        //    return true;
            
        //}
        //con.Close();
        //return false;
        var context = new WebBanHoaDataContext();
        var user = context.UserNs.SingleOrDefault(x => x.UserName == name && x.Password == pass);
        if (user == null)
            return false;
        else
            return true;

    }

    public static bool ktraAdmin(UserDTO ndDTO)
    {
        string sQuery = "select * from UserN where UserName='" + ndDTO.Username + "' and [Password]='"
            + MaHoa(ndDTO.Username, ndDTO.Pass) + "' and Typeuser = "+ 1 ;
        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        DataProvider.DongKetNoi(con);
        if (dt.Rows.Count > 0)
        {
            return true;
        }
        return false;
    }
    public static bool ktraQuenPassword(UserDTO ndDTO)
    {
        string sQuery = "select * from UserN where UserName='" + ndDTO.Username + "' and Email='" + ndDTO.Mail + "'";
        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        DataProvider.DongKetNoi(con);
        if (dt.Rows.Count > 0)
        {
            return true;
        }
        return false;
    }
    public static string LayPassword(UserDTO ndDTO)
    {
        string ketqua;
        string sQuery = " select [Password] from UserN where UserName='" + ndDTO.Username + "'";
        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        DataProvider.DongKetNoi(con);
        ketqua = dt.Rows[0][0].ToString();
        ketqua = GiaiMa(ndDTO.Username, ketqua);
        return ketqua;
    }
    public static DataTable LayUserName(UserDTO ndDTO)
    {
        string sQuery = "select * from UserN where UserName='" + ndDTO.Username + "'";
        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        DataProvider.DongKetNoi(con);
        return dt;
    }

    #endregion
    #region themThongTin
    public static string MaHoa(string key, string toEn)
    {
        byte[] keyArray;
        byte[] toEndcry = UTF8Encoding.UTF8.GetBytes(toEn);
        MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
        keyArray = md5.ComputeHash(UTF32Encoding.UTF8.GetBytes(key));
        TripleDESCryptoServiceProvider trip = new TripleDESCryptoServiceProvider();
        trip.Key = keyArray;
        trip.Mode = CipherMode.ECB;
        trip.Padding = PaddingMode.PKCS7;
        ICryptoTransform tranform = trip.CreateEncryptor();
        byte[] resualArray = tranform.TransformFinalBlock(toEndcry, 0, toEndcry.Length);
        return Convert.ToBase64String(resualArray, 0, resualArray.Length);
    }
    public static string GiaiMa(string key, string toDe)
    {
        byte[] keyArray = null;
        byte[] toEndArray = Convert.FromBase64String(toDe);
        MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
        keyArray = md5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
        TripleDESCryptoServiceProvider trip = new TripleDESCryptoServiceProvider();
        trip.Key = keyArray;
        trip.Mode = CipherMode.ECB;
        trip.Padding = PaddingMode.PKCS7;
        ICryptoTransform tranfrom = trip.CreateDecryptor();
        byte[] resualArray = tranfrom.TransformFinalBlock(toEndArray, 0, toEndArray.Length);
        return UTF32Encoding.UTF8.GetString(resualArray);

    }
    public static bool themUser(UserDTO ndDTO)
    {
        try
        {
            string sPassEncode = MaHoa(ndDTO.Username.Trim(), ndDTO.Pass.Trim());
            string sQuery = "Insert into UserN(UserName,[Password],Typeuser,Email) values('"
                + ndDTO.Username + "','"+ ndDTO.Pass + "',2,'" + ndDTO.Mail + "')";
       
                SqlConnection con = DataProvider.TaoKetNoi();
               
                bool ketqua = DataProvider.ExecuteNonQuery(sQuery, con);
                
                DataProvider.DongKetNoi(con);
                return ketqua;
        }
        catch (Exception ex)
        {

            return false;
        }

        }




    public static bool AdminThemUser(UserDTO ndDTO)
    {
        string sPassEncode = MaHoa(ndDTO.Username, ndDTO.Pass);
        string sQuery = "insert into UserN(UserName,[Password],Typeuser,Email) values('" + ndDTO.Username + "','" +
            sPassEncode + "'," + ndDTO.LoaiND + ",'" + ndDTO.Mail + "')";
        SqlConnection con = DataProvider.TaoKetNoi();
        bool ketqua = DataProvider.ExecuteNonQuery(sQuery, con);

        DataProvider.DongKetNoi(con);
        return ketqua;
    }
    public static void DoiPassword(UserDTO ndDTO)
    {
        string sNewPassEncode = MaHoa(ndDTO.Username,ndDTO.Newpass);
        string sQuery = "UPDATE UserN SET [Password]='" + sNewPassEncode + "'WHERE UserName='" + ndDTO.Username + "'";
        SqlConnection con = DataProvider.TaoKetNoi();
        DataProvider.ExecuteNonQuery(sQuery, con);
        DataProvider.DongKetNoi(con);
        
    }

    public static void EditInfo(UserDTO ndDTO)
    {
        string sNewPassEncode = MaHoa(ndDTO.Username, ndDTO.Newpass);
        string sQuery = "update UserN set [Password] ='"+ sNewPassEncode +"',Typeuser ="+ ndDTO.LoaiND +",Email ='"+ ndDTO.Mail +"' where UserName ='"+ndDTO.Username+ "'";
       SqlConnection con = DataProvider.TaoKetNoi();
        DataProvider.ExecuteNonQuery(sQuery, con);
        DataProvider.DongKetNoi(con);
    }
    public static void XoaUser(UserDTO ndDTO)
    {
        string sQuery = "delete from UserN where UserName ='" + ndDTO.Username + "'";
        SqlConnection con = DataProvider.TaoKetNoi();
        DataProvider.ExecuteNonQuery(sQuery, con);
        DataProvider.DongKetNoi(con);
    }
    #endregion
}